﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="OleDbQueryUtility.cs" company="CodePlex">
//   Copyright © CodePlex 2012
// </copyright>
// <summary>
//   Defines the OleDbQueryUtility type.
// </summary>
// --------------------------------------------------------------------------------------------------------------------

namespace DataFramework.QueryUtilities
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Diagnostics.CodeAnalysis;
    using System.Linq;
    using System.Text;

    using DataFramework.Attributes;
    using DataFramework.QueryConditions;

    /// <summary>
    /// A query utility that builds queries that target the Ole DB connection / command objects.
    /// </summary>
    public sealed class OleDbQueryUtility : BaseQueryUtility
    {
        /// <summary>
        /// Instance object for this singleton.
        /// </summary>
        private static volatile OleDbQueryUtility instance;

        /// <summary>
        /// Initializes a new instance of the <see cref="OleDbQueryUtility"/> class.
        /// </summary>
        [SuppressMessage("StyleCop.CSharp.DocumentationRules",
            "SA1642:ConstructorSummaryDocumentationMustBeginWithStandardText",
            Justification = "Reviewed. Suppression is OK here.")]
        private OleDbQueryUtility()
        {
        }

        /// <summary>
        /// Gets the instance.
        /// </summary>
        /// <value>The instance.</value>
        public static new OleDbQueryUtility Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (SyncRoot)
                    {
                        if (instance == null)
                        {
                            instance = new OleDbQueryUtility();
                        }
                    }
                }

                return instance;
            }
        }

        /// <summary>
        /// Builds the select statement for items.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="cmd">The CMD.</param>
        /// <param name="itemType">Type of the item.</param>
        /// <param name="conditions">The conditions.</param>
        internal override void BuildSelectStatementForItems(string tableName, IDbCommand cmd, Type itemType, IEnumerable<BaseQueryCondition> conditions)
        {
            var columnBuffer = new StringBuilder();
            var properties = itemType.GetProperties();
            foreach (var property in properties)
            {
                var columnMappings = property.GetCustomAttributes(true).OfType<ColumnMapping>().ToList();
                foreach (var mapping in columnMappings)
                {
                    columnBuffer.Append(columnBuffer.Length == 0 ? string.Format("{0}", mapping.ColumnName) : string.Format(", {0}", mapping.ColumnName));
                }
            }

            var buffer = new StringBuilder(string.Format(BaseSelectString, columnBuffer, tableName));
            if (conditions != null)
            {
                buffer.Append(this.BuildWhereClauseForSelect(cmd, itemType, conditions));
            }

            cmd.CommandText = buffer.ToString();
        }

        /// <summary>
        /// Builds the insert statement for item.
        /// </summary>
        /// <param name="item">The item.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="cmd">The CMD.</param>
        internal override void BuildInsertStatementForItem(object item, string tableName, IDbCommand cmd)
        {
            var columnBuffer = new StringBuilder();
            var parameterBuffer = new StringBuilder();
            string columnName = null;
            var columnType = DbType.String;
            var includeInStatement = true;

            var properties = item.GetType().GetProperties();
            foreach (var property in properties)
            {
                foreach (var customAttribute in property.GetCustomAttributes(true).OfType<ColumnMapping>())
                {
                    columnName = customAttribute.ColumnName;
                    columnType = customAttribute.DbType;
                    includeInStatement = !customAttribute.IsKey || (customAttribute.IsKey && !customAttribute.IsAutoId);
                }

                var value = property.GetValue(item, null);
                value = this.DatabaseHelper.ConvertValueToDbSafeValue(value);

                if (!string.IsNullOrEmpty(columnName) && includeInStatement)
                {
                    columnBuffer.Append(columnBuffer.Length == 0 ? string.Format("{0}", columnName) : string.Format(", {0}", columnName));

                    // NOTE: OleDB parameters are index based, not name based. If you include the name on them an exception will occur.
                    parameterBuffer.Append(parameterBuffer.Length == 0 ? string.Format("{0}", this.DatabaseHelper.QueryParameterPrefix) : string.Format(", {0}", this.DatabaseHelper.QueryParameterPrefix));

                    cmd.Parameters.Add(this.DatabaseHelper.CreateParameter(columnName, columnType, value));
                }
            }

            cmd.CommandText = string.Format(BaseInsertString, tableName, columnBuffer, parameterBuffer);
        }

        /// <summary>
        /// Builds the update statement for item.
        /// </summary>
        /// <param name="item">The item.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="cmd">The CMD.</param>
        internal override void BuildUpdateStatementForItem(object item, string tableName, IDbCommand cmd)
        {
            var setClauseBuffer = new StringBuilder();
            var whereClauseBuffer = new StringBuilder();
            var whereParameters = new List<IDataParameter>();

            var properties = item.GetType().GetProperties();
            foreach (var property in properties)
            {
                foreach (var customAttribute in property.GetCustomAttributes(true).OfType<ColumnMapping>())
                {
                    var value = property.GetValue(item, null);
                    value = this.DatabaseHelper.ConvertValueToDbSafeValue(value);

                    if (!string.IsNullOrEmpty(customAttribute.ColumnName))
                    {
                        if (!customAttribute.IsKey)
                        {
                            // NOTE: OleDB parameters are index based, not name based. If you include the name on them an exception will occur.
                            setClauseBuffer.Append(setClauseBuffer.Length == 0 ? string.Format("{1} = {0}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName) : string.Format(", {1} = {0}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName));
                            cmd.Parameters.Add(this.DatabaseHelper.CreateParameter(customAttribute.ColumnName, customAttribute.DbType, value));
                        }
                        else
                        {
                            // NOTE: OleDB parameters are index based, not name based. If you include the name on them an exception will occur.
                            whereClauseBuffer.Append(whereClauseBuffer.Length == 0 ? string.Format("{1} = {0}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName) : string.Format("AND {1} = {0}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName));
                        }

                        if (customAttribute.IsKey)
                        {
                            whereParameters.Add(this.DatabaseHelper.CreateParameter(customAttribute.ColumnName, customAttribute.DbType, value));
                        }
                    }
                }
            }

            cmd.CommandText = string.Format(BaseUpdateString, tableName, setClauseBuffer, whereClauseBuffer);

            foreach (var parm in whereParameters)
            {
                cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// Builds the delete statement for item.
        /// </summary>
        /// <param name="item">The item.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="cmd">The CMD.</param>
        internal override void BuildDeleteStatementForItem(object item, string tableName, IDbCommand cmd)
        {
            var whereClauseBuffer = new StringBuilder();

            var properties = item.GetType().GetProperties();
            foreach (var property in properties)
            {
                foreach (var customAttribute in property.GetCustomAttributes(true).OfType<ColumnMapping>().Where(m => m.IsKey))
                {
                    var value = property.GetValue(item, null);
                    value = this.DatabaseHelper.ConvertValueToDbSafeValue(value);

                    if (!string.IsNullOrEmpty(customAttribute.ColumnName))
                    {
                        // NOTE: OleDB parameters are index based, not name based. If you include the name on them an exception will occur.
                        whereClauseBuffer.Append(whereClauseBuffer.Length == 0 ? string.Format("{1} = {0}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName) : string.Format(", {1} = {0}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName));

                        cmd.Parameters.Add(this.DatabaseHelper.CreateParameter(customAttribute.ColumnName, customAttribute.DbType, value));
                    }
                }
            }

            cmd.CommandText = string.Format(BaseDeleteString, tableName, whereClauseBuffer);
        }
    }
}
